﻿using System;
using System.Collections.Generic;

using MySql.Data.MySqlClient;



namespace StagingAreaETL
{
    class SimpleTransport
    {
        internal void CarryOverTime()
        {
            //first simply transport TIMEs from SA.LIB_TIME to ODS.TIME
            MySqlConnection connection = DBHelper.getSAConnection();
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = "insert into `ods`.`time` " +
                                  " select * from lib_time;";
            command.ExecuteNonQuery();

            //transform and save TIMEs from SA.TIME
            HashSet<TimeDIM> times = new HashSet<TimeDIM>();
            command.CommandText = "select * from time";
            MySqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                string key = reader.GetString(0);

                string[] ar = key.Split(new [] {'-', ' ', ':'});
                TimeDIM time = new TimeDIM(new DateTime(Int32.Parse(ar[0]), Int32.Parse(ar[1]), Int32.Parse(ar[2]),
                                                        Int32.Parse(ar[3]), Int32.Parse(ar[4]), Int32.Parse(ar[5])));
                times.Add(time);
            }

            connection.Close();

            connection = DBHelper.getODSConnection();
            command = connection.CreateCommand();

            foreach (TimeDIM time in times)
            {
                command.CommandText = "insert into time values ('" + time.TimeKEY + "', '" + time.Date + "', '" + time.DayTime + "');";
                command.ExecuteNonQuery();
            }

            connection.Close();
        }


        internal void CarryOverBorrowings()
        {
            MySqlConnection connection = DBHelper.getSAConnection();
            MySqlCommand command = connection.CreateCommand();

            command.CommandText = "insert into `ods`.`BorrowingFacts` " +
                                  " SELECT startTimeKEY, endTimeKEY, libraryAccountKEY, libraryKEY, bookKEY FROM lib_borrowingfct;";
            command.ExecuteNonQuery();

            connection.Close();
        }

        /// <summary>
        /// Carry over TIME from SA to ODS. TimeKEYs conversions are made.
        /// </summary>
        internal void CarryOverSessions()
        {
            MySqlConnection connection = DBHelper.getSAConnection();
            MySqlCommand command = connection.CreateCommand();

            command.CommandText = "insert into `ods`.`SessionFacts` " +
                                  " SELECT UserAccountKey, RoomKey, ComputerKey, " +
                                  " REPLACE(REPLACE(StartTimeKey, ' ', '-'), ':', '-') as StartTimeKey, " +
                                  " REPLACE(REPLACE(EndTimeKey, ' ', '-'), ':', '-') as EndTimeKey " +
                                  " FROM `stagingarea`.`sessionfacts`;";
            command.ExecuteNonQuery();

            connection.Close();
        }

    }
}
